*********************************************************************** 
9/11/13: ADDED 2012
12/18/12: ADDED 2011
10/18/11: ADDED 2010
11/2/10: ADDED 2009

12/21/09: ADDED 2008

8/27/09: ADDED 2006 & 2007

9/9/08: CREATED
   VERIFIED THAT NUMBERS MATCH EXACTLY THOSE FROM TEH FNA OR FN2 DETAILED 
   EXPENDITURE FILE IN 2005:1

***********************************************************************;
OPTIONS NOLABEL;

LIBNAME alldat '~/jim/data';
*%include "~/jim/gzipseq.mcr";

filename mtab801 pipe 'gunzip -c ~/jim/data/ce_mtab/1980_81/08423-0003-Data.txt.gz';
filename mtab802 pipe 'gunzip -c ~/jim/data/ce_mtab/1980_81/08423-0007-Data.txt.gz';
filename mtab803 pipe 'gunzip -c ~/jim/data/ce_mtab/1980_81/08423-0011-Data.txt.gz';
filename mtab804 pipe 'gunzip -c ~/jim/data/ce_mtab/1980_81/08423-0015-Data.txt.gz';
 
filename mtab811 pipe 'gunzip -c ~/jim/data/ce_mtab/1980_81/08423-0019-Data.txt.gz';
filename mtab812 pipe 'gunzip -c ~/jim/data/ce_mtab/1980_81/08423-0023-Data.txt.gz';
filename mtab813 pipe 'gunzip -c ~/jim/data/ce_mtab/1980_81/08423-0027-Data.txt.gz';
filename mtab814 pipe 'gunzip -c ~/jim/data/ce_mtab/1980_81/08423-0031-Data.txt.gz';
filename mtab815 pipe 'gunzip -c ~/jim/data/ce_mtab/1980_81/08423-0035-Data.txt.gz';

filename mtab841 pipe 'gunzip -c ~/jim/data/ce_mtab/1984/08671-0003-Data.txt.gz';
filename mtab842 pipe 'gunzip -c ~/jim/data/ce_mtab/1984/08671-0007-Data.txt.gz';
filename mtab843 pipe 'gunzip -c ~/jim/data/ce_mtab/1984/08671-0011-Data.txt.gz';
filename mtab844 pipe 'gunzip -c ~/jim/data/ce_mtab/1984/08671-0015-Data.txt.gz';
 
filename mtab851 pipe 'gunzip -c ~/jim/data/ce_mtab/1985/08904-0003-Data.txt.gz';
filename mtab852 pipe 'gunzip -c ~/jim/data/ce_mtab/1985/08904-0007-Data.txt.gz';
filename mtab853 pipe 'gunzip -c ~/jim/data/ce_mtab/1985/08904-0011-Data.txt.gz';
filename mtab854 pipe 'gunzip -c ~/jim/data/ce_mtab/1985/08904-0015-Data.txt.gz';
filename mtab855 pipe 'gunzip -c ~/jim/data/ce_mtab/1985/08904-0019-Data.txt.gz';
 
filename mtab861 pipe 'gunzip -c ~/jim/data/ce_mtab/1986/09113-0003-Data.txt.gz';
filename mtab862 pipe 'gunzip -c ~/jim/data/ce_mtab/1986/09113-0007-Data.txt.gz';
filename mtab863 pipe 'gunzip -c ~/jim/data/ce_mtab/1986/09113-0011-Data.txt.gz';
filename mtab864 pipe 'gunzip -c ~/jim/data/ce_mtab/1986/09113-0015-Data.txt.gz';
 
filename mtab871 pipe 'gunzip -c ~/jim/data/ce_mtab/1987/09332-0003-Data.txt.gz';
filename mtab872 pipe 'gunzip -c ~/jim/data/ce_mtab/1987/09332-0007-Data.txt.gz';
filename mtab873 pipe 'gunzip -c ~/jim/data/ce_mtab/1987/09332-0011-Data.txt.gz';
filename mtab874 pipe 'gunzip -c ~/jim/data/ce_mtab/1987/09332-0015-Data.txt.gz';

filename mtab881 pipe 'gunzip -c ~/jim/data/ce_mtab/1988/09451-0003-Data.txt.gz';
filename mtab882 pipe 'gunzip -c ~/jim/data/ce_mtab/1988/09451-0007-Data.txt.gz';
filename mtab883 pipe 'gunzip -c ~/jim/data/ce_mtab/1988/09451-0011-Data.txt.gz';
filename mtab884 pipe 'gunzip -c ~/jim/data/ce_mtab/1988/09451-0015-Data.txt.gz';
 
filename mtab891 pipe 'gunzip -c ~/jim/data/ce_mtab/1989/09712-0003-Data.txt.gz';
filename mtab892 pipe 'gunzip -c ~/jim/data/ce_mtab/1989/09712-0007-Data.txt.gz';
filename mtab893 pipe 'gunzip -c ~/jim/data/ce_mtab/1989/09712-0011-Data.txt.gz';
filename mtab894 pipe 'gunzip -c ~/jim/data/ce_mtab/1989/09712-0015-Data.txt.gz';
 
filename mtab901 pipe 'gunzip -c ~/jim/data/ce_mtab/1990/09820-0003-Data.txt.gz';
filename mtab902 pipe 'gunzip -c ~/jim/data/ce_mtab/1990/09820-0007-Data.txt.gz';
filename mtab903 pipe 'gunzip -c ~/jim/data/ce_mtab/1990/09820-0011-Data.txt.gz';
filename mtab904 pipe 'gunzip -c ~/jim/data/ce_mtab/1990/09820-0015-Data.txt.gz';
 
filename mtab911 pipe 'gunzip -c ~/jim/data/ce_mtab/1991/06209-0003-Data.txt.gz';
filename mtab912 pipe 'gunzip -c ~/jim/data/ce_mtab/1991/06209-0007-Data.txt.gz';
filename mtab913 pipe 'gunzip -c ~/jim/data/ce_mtab/1991/06209-0011-Data.txt.gz';
filename mtab914 pipe 'gunzip -c ~/jim/data/ce_mtab/1991/06209-0015-Data.txt.gz';

filename mtab921 pipe 'gunzip -c ~/jim/data/ce_mtab/1992/06372-0003-Data.txt.gz';
filename mtab922 pipe 'gunzip -c ~/jim/data/ce_mtab/1992/06372-0007-Data.txt.gz';
filename mtab923 pipe 'gunzip -c ~/jim/data/ce_mtab/1992/06372-0011-Data.txt.gz';
filename mtab924 pipe 'gunzip -c ~/jim/data/ce_mtab/1992/06372-0015-Data.txt.gz';
 
filename mtab931 pipe 'gunzip -c ~/jim/data/ce_mtab/1993/da6580.mtab.q931.gz';   
filename mtab932 pipe 'gunzip -c ~/jim/data/ce_mtab/1993/da6580.mtab.q932.gz';   
filename mtab933 pipe 'gunzip -c ~/jim/data/ce_mtab/1993/da6580.mtab.q933.gz';   
filename mtab934 pipe 'gunzip -c ~/jim/data/ce_mtab/1993/da6580.mtab.q934.gz';      
 
filename mtab941 pipe 'gunzip -c ~/jim/data/ce_mtab/1994/da6710.mtab.q941.gz';      
filename mtab942 pipe 'gunzip -c ~/jim/data/ce_mtab/1994/da6710.mtab.q942.gz';      
filename mtab943 pipe 'gunzip -c ~/jim/data/ce_mtab/1994/da6710.mtab.q943.gz';   
filename mtab944 pipe 'gunzip -c ~/jim/data/ce_mtab/1994/da6710.mtab.q944.gz';      
 
filename mtab951 pipe 'gunzip -c ~/jim/data/ce_mtab/1995/da2264.mtab.q951.gz';      
filename mtab952 pipe 'gunzip -c ~/jim/data/ce_mtab/1995/da2264.mtab.q952.gz';      
filename mtab953 pipe 'gunzip -c ~/jim/data/ce_mtab/1995/da2264.mtab.q953.gz';      
filename mtab954 pipe 'gunzip -c ~/jim/data/ce_mtab/1995/da2264.mtab.q954.gz';   
filename mtab955 pipe 'gunzip -c ~/jim/data/ce_mtab/1995/da2264.mtab.q955.gz';
 
filename mtab961 pipe 'gunzip -c ~/jim/data/ce_mtab/1996/02794-0003-Data.txt.gz';
filename mtab962 pipe 'gunzip -c ~/jim/data/ce_mtab/1996/02794-0007-Data.txt.gz';
filename mtab963 pipe 'gunzip -c ~/jim/data/ce_mtab/1996/02794-0011-Data.txt.gz';
filename mtab964 pipe 'gunzip -c ~/jim/data/ce_mtab/1996/02794-0015-Data.txt.gz';
 
filename mtab971 pipe 'gunzip -c ~/jim/data/ce_mtab/1997/mtabi971X.txt.gz';      
filename mtab972 pipe 'gunzip -c ~/jim/data/ce_mtab/1997/mtabi972.txt.gz'; 
filename mtab973 pipe 'gunzip -c ~/jim/data/ce_mtab/1997/mtabi973.txt.gz'; 
filename mtab974 pipe 'gunzip -c ~/jim/data/ce_mtab/1997/mtabi974.txt.gz';
 
filename mtab981 pipe 'gunzip -c ~/jim/data/ce_mtab/1998/da2971.mtab981.gz';     
filename mtab982 pipe 'gunzip -c ~/jim/data/ce_mtab/1998/da2971.mtab982.gz';     
filename mtab983 pipe 'gunzip -c ~/jim/data/ce_mtab/1998/da2971.mtab983.gz';     
filename mtab984 pipe 'gunzip -c ~/jim/data/ce_mtab/1998/da2971.mtab984.gz';

filename mtab991 pipe 'gunzip -c ~/jim/data/ce_mtab/1999/da3228.mtab991.gz';
filename mtab992 pipe 'gunzip -c ~/jim/data/ce_mtab/1999/da3228.mtab992.gz'; 
filename mtab993 pipe 'gunzip -c ~/jim/data/ce_mtab/1999/da3228.mtab993.gz'; 
filename mtab994 pipe 'gunzip -c ~/jim/data/ce_mtab/1999/da3228.mtab994.gz'; 
 
filename mtab1001 pipe 'gunzip -c ~/jim/data/ce_mtab/2000/da3396.mtab001.gz';
filename mtab1002 pipe 'gunzip -c ~/jim/data/ce_mtab/2000/da3396.mtab002.gz';     
filename mtab1003 pipe 'gunzip -c ~/jim/data/ce_mtab/2000/da3396.mtab003.gz';
filename mtab1004 pipe 'gunzip -c ~/jim/data/ce_mtab/2000/da3396.mtab004.gz';
 
filename mtab1011 pipe 'gunzip -c ~/jim/data/ce_mtab/2001/da3674.mtab011.gz';
filename mtab1012 pipe 'gunzip -c ~/jim/data/ce_mtab/2001/da3674.mtab012.gz';        
filename mtab1013 pipe 'gunzip -c ~/jim/data/ce_mtab/2001/da3674.mtab013.gz';     
filename mtab1014 pipe 'gunzip -c ~/jim/data/ce_mtab/2001/da3674.mtab014.gz';
 
filename mtab1021 pipe 'gunzip -c ~/jim/data/ce_mtab/2002/03949-0003-Data.txt.gz';
filename mtab1022 pipe 'gunzip -c ~/jim/data/ce_mtab/2002/03949-0007-Data.txt.gz';
filename mtab1023 pipe 'gunzip -c ~/jim/data/ce_mtab/2002/03949-0011-Data.txt.gz';
filename mtab1024 pipe 'gunzip -c ~/jim/data/ce_mtab/2002/03949-0015-Data.txt.gz';
 
filename mtab1031 pipe 'gunzip -c ~/jim/data/ce_mtab/2003/mtabi031x.txt.gz'; 
filename mtab1032 pipe 'gunzip -c ~/jim/data/ce_mtab/2003/mtabi032.txt.gz';  
filename mtab1033 pipe 'gunzip -c ~/jim/data/ce_mtab/2003/mtabi033.txt.gz';  
filename mtab1034 pipe 'gunzip -c ~/jim/data/ce_mtab/2003/mtabi034.txt.gz';  
 
filename mtab1041 pipe 'gunzip -c ~/jim/data/ce_mtab/2004/mtabi041x.txt.gz'; 
filename mtab1042 pipe 'gunzip -c ~/jim/data/ce_mtab/2004/mtabi042.txt.gz';  
filename mtab1043 pipe 'gunzip -c ~/jim/data/ce_mtab/2004/mtabi043.txt.gz';  
filename mtab1044 pipe 'gunzip -c ~/jim/data/ce_mtab/2004/mtabi044.txt.gz';  
filename mtab1045 pipe 'gunzip -c ~/jim/data/ce_mtab/2004/mtabi051.txt.gz';  

filename mtab1051 pipe 'gunzip -c ~/jim/data/ce_mtab/2005/mtabi051x.txt.gz'; 
filename mtab1052 pipe 'gunzip -c ~/jim/data/ce_mtab/2005/mtabi052.txt.gz';  
filename mtab1053 pipe 'gunzip -c ~/jim/data/ce_mtab/2005/mtabi053.txt.gz';  
filename mtab1054 pipe 'gunzip -c ~/jim/data/ce_mtab/2005/mtabi054.txt.gz';  

filename mtab1061 pipe 'gunzip -c ~/jim/data/ce_mtab/2006/mtabi061x.txt.gz'; 
filename mtab1062 pipe 'gunzip -c ~/jim/data/ce_mtab/2006/mtabi062.txt.gz';  
filename mtab1063 pipe 'gunzip -c ~/jim/data/ce_mtab/2006/mtabi063.txt.gz';  
filename mtab1064 pipe 'gunzip -c ~/jim/data/ce_mtab/2006/mtabi064.txt.gz';  

filename mtab1071 pipe 'gunzip -c ~/jim/data/ce_mtab/2007/mtabi071x.txt.gz'; 
filename mtab1072 pipe 'gunzip -c ~/jim/data/ce_mtab/2007/mtabi072.txt.gz';  
filename mtab1073 pipe 'gunzip -c ~/jim/data/ce_mtab/2007/mtabi073.txt.gz';  
filename mtab1074 pipe 'gunzip -c ~/jim/data/ce_mtab/2007/mtabi074.txt.gz';  

filename mtab1081 pipe 'gunzip -c ~/jim/data/ce_mtab/2008/mtabi081x.txt.gz'; 
filename mtab1082 pipe 'gunzip -c ~/jim/data/ce_mtab/2008/mtabi082.txt.gz';  
filename mtab1083 pipe 'gunzip -c ~/jim/data/ce_mtab/2008/mtabi083.txt.gz';  
filename mtab1084 pipe 'gunzip -c ~/jim/data/ce_mtab/2008/mtabi084.txt.gz';  

filename mtab1091 pipe 'gunzip -c ~/jim/data/ce_mtab/2009/mtabi091x.txt.gz';
filename mtab1092 pipe 'gunzip -c ~/jim/data/ce_mtab/2009/mtabi092.txt.gz'; 
filename mtab1093 pipe 'gunzip -c ~/jim/data/ce_mtab/2009/mtabi093.txt.gz'; 
filename mtab1094 pipe 'gunzip -c ~/jim/data/ce_mtab/2009/mtabi094.txt.gz'; 

filename mtab1101 pipe 'gunzip -c ~/jim/data/ce_mtab/2010/mtabi101x.txt.gz';
filename mtab1102 pipe 'gunzip -c ~/jim/data/ce_mtab/2010/mtabi102.txt.gz'; 
filename mtab1103 pipe 'gunzip -c ~/jim/data/ce_mtab/2010/mtabi103.txt.gz'; 
filename mtab1104 pipe 'gunzip -c ~/jim/data/ce_mtab/2010/mtabi104.txt.gz'; 

***************************************************************************
* MACRO FOR SURVEY YEARS 1980-2010
***************************************************************************;
%MACRO read(qyr); 
 
  DATA exp&qyr;
   INFILE mtab&qyr ; 
   IF 801 LE &qyr LE 955 THEN INPUT newid 1-8 ucc 9-14 cost 15-26 ref_mo 30-31 ref_yr 32-33;
      ELSE IF &qyr GE 961 THEN INPUT newid 1-8 ucc 9-14 cost 15-26 ref_mo 30-31 ref_yr 32-35;

   IF ucc IN (6001 6002);

   qyear=&qyr; 
   IF qyear LE 855 THEN newid=9000000+newid;
	
  RUN;

PROC SORT DATA=exp&qyr; 
   BY newid;
RUN; 
PROC MEANS DATA=exp&qyr SUM NOPRINT;
     BY newid;
     ID qyear;
     VAR cost;
     OUTPUT OUT=out_debt&qyr(DROP=_FREQ_ _TYPE_) SUM=tot_debt;
RUN;  
PROC MEANS; 
RUN; 
%MEND read; 


***************************************************************************
* MACRO FOR SURVEY YEARS 2011 AND AFTER
***************************************************************************;
%MACRO read2(qyr,ext,yr4);

  PROC IMPORT DATAFILE="~/jim/data/ce_mtab/&yr4/mtbi&ext..csv"
     OUT=mtab_dat
     DBMS=CSV
     REPLACE;
     GETNAMES=YES;
  RUN;
 
DATA mtab_dat; 
   SET mtab_dat(RENAME=(ucc=temp1 ref_mo=temp2 ref_yr=temp3)); 
 
   ucc=temp1*1;   
   ref_mo=temp2*1;   
   ref_yr=temp3*1;   

   IF ucc IN (6001 6002);

   qyear=&qyr; 

PROC SORT DATA=mtab_dat; 
   BY newid;
RUN; 
PROC MEANS DATA=mtab_dat SUM NOPRINT;
     BY newid;
     ID qyear;
     VAR cost;
     OUTPUT OUT=out_debt&qyr(DROP=_FREQ_ _TYPE_) SUM=tot_debt;
RUN;  
PROC MEANS; 
RUN; 

%MEND read2;


%read(801);
%read(802);
%read(803); 
%read(804);   
      
%read(811); 
%read(812); 
%read(813); 
%read(814); 
%read(815);    

%read(841);
%read(842);    
%read(843);
%read(844);
 
%read(851);
%read(852);    
%read(853); 
%read(854);   
%read(855);   
 
%read(861); 
%read(862);
%read(863);
%read(864); 

%read(871);
%read(872); 
%read(873);   
%read(874);
 
%read(881);    
%read(882); 
%read(883);
%read(884);
 
%read(891);
%read(892); 
%read(893);
%read(894);  
 
%read(901); 
%read(902);
%read(903);
%read(904); 
 
%read(911);    
%read(912); 
%read(913);   
%read(914);
 
%read(921);    
%read(922);
%read(923);
%read(924);
 
%read(931);    
%read(932); 
%read(933);   
%read(934); 

%read(941);   
%read(942); 
%read(943);  
%read(944); 
 
%read(951);
%read(952);
%read(953);
%read(954); 
%read(955); 
      
%read(961); 
%read(962);  
%read(963); 
%read(964);
 
%read(971);
%read(972);
%read(973); 
%read(974);
 
%read(981); 
%read(982);  
%read(983); 
%read(984);
 
%read(991);
%read(992);
%read(993); 
%read(994);   
      
%read(1001); 
%read(1002);  
%read(1003); 
%read(1004);

%read(1011);
%read(1012);
%read(1013); 
%read(1014);
 
%read(1021); 
%read(1022);  
%read(1023); 
%read(1024);
 
%read(1031);
%read(1032);
%read(1033); 
%read(1034);   
      
%read(1041); 
%read(1042);  
%read(1043); 
%read(1044);
%read(1045);

%read(1051); 
%read(1052);  
%read(1053); 
%read(1054);

%read(1061); 
%read(1062);  
%read(1063); 
%read(1064);

%read(1071); 
%read(1072);  
%read(1073); 
%read(1074);

%read(1081); 
%read(1082);  
%read(1083); 
%read(1084);

%read(1091);
%read(1092);
%read(1093);
%read(1094);

%read(1101);
%read(1102);
%read(1103);
%read(1104);

%read2(1111,111x,2011);
%read2(1112,112,2011);
%read2(1113,113,2011);
%read2(1114,114,2011);

%read2(1121,121x,2012);
%read2(1122,122,2012);
%read2(1123,123,2012);
%read2(1124,124,2012);

%read2(1131,131x,2013);

DATA alldat.debt_val80_13;
SET     out_debt801 out_debt802 out_debt803 out_debt804
	out_debt811 out_debt812 out_debt813 out_debt814 out_debt815 
	out_debt841 out_debt842 out_debt843 out_debt844
        out_debt851 out_debt852 out_debt853 out_debt854 out_debt855
        out_debt861 out_debt862 out_debt863 out_debt864
        out_debt871 out_debt872 out_debt873 out_debt874
        out_debt881 out_debt882 out_debt883 out_debt884
        out_debt891 out_debt892 out_debt893 out_debt894
        out_debt901 out_debt902 out_debt903 out_debt904
        out_debt911 out_debt912 out_debt913 out_debt914
        out_debt921 out_debt922 out_debt923 out_debt924
        out_debt931 out_debt932 out_debt933 out_debt934
        out_debt941 out_debt942 out_debt943 out_debt944
        out_debt951 out_debt952 out_debt953 out_debt954 out_debt955
        out_debt961 out_debt962 out_debt963 out_debt964
        out_debt971 out_debt972 out_debt973 out_debt974
        out_debt981 out_debt982 out_debt983 out_debt984
        out_debt991 out_debt992 out_debt993 out_debt994
        out_debt1001 out_debt1002 out_debt1003 out_debt1004
        out_debt1011 out_debt1012 out_debt1013 out_debt1014
        out_debt1021 out_debt1022 out_debt1023 out_debt1024
        out_debt1031 out_debt1032 out_debt1033 out_debt1034
        out_debt1041 out_debt1042 out_debt1043 out_debt1044 out_debt1045
        out_debt1051 out_debt1052 out_debt1053 out_debt1054 
        out_debt1061 out_debt1062 out_debt1063 out_debt1064
        out_debt1071 out_debt1072 out_debt1073 out_debt1074  
        out_debt1081 out_debt1082 out_debt1083 out_debt1084     
        out_debt1091 out_debt1092 out_debt1093 out_debt1094     
        out_debt1101 out_debt1102 out_debt1103 out_debt1104 
        out_debt1111 out_debt1112 out_debt1113 out_debt1114 
        out_debt1121 out_debt1122 out_debt1123 out_debt1124 
        out_debt1131;     
RUN;

PROC MEANS;
RUN;

PROC SORT DATA=alldat.debt_val80_13;
BY qyear;
RUN;
PROC MEANS DATA=alldat.debt_val80_13;
BY qyear;
RUN;

PROC SORT DATA=alldat.debt_val80_13;
BY newid;
RUN;
PROC SORT DATA=alldat.debt_val80_12;
BY newid;
RUN;

PROC COMPARE BASE=alldat.debt_val80_13
	     COMPARE=alldat.debt_val80_12;
   WHERE qyear LE 1124;
RUN;


***************************************************************************
EXPORT DATA AS STATA DATA FILE FOR COMPARISON
***************************************************************************;
DATA temp_stata;
SET alldat.debt_val80_13;
*IF qyear GT 1084;
RUN;

proc export data=temp_stata 
	OUTFILE="~/jim/data/data_for_others/debt_val8013.dta"
	REPLACE;
run;


 
